Advanced Sorting in Discover Pro

This advanced sorting example shows how to sort a grid that ranks the football teams in the English Premier League according to their points, goal difference, and goals for and against. You will learn how to use advanced features like nested Sort Chips and Sort by, then by settings to position teams in a sequence based on their wins, losses, and draws, goal difference, and so on.

Tip: Because the Sorting functionality in Discover is both very flexible and very powerful, we recommend that you "walkthrough" some different examples to see how different settings made in the wizard interact to produce the overall functionality. For a more basic sorting example than seen here, see Basic Sorting in Discover Pro.

Using the Sorting Wizard

Objective

In this example, we use some data from the English Premier League to create a list of football teams that is sorted to show us which teams are currently top and bottom of the league by some of their criteria. We sort by points, with tiebreakers based on goal difference, goals for, and goals against. We also split the data by points scored at home or away, although the overall ranking always relates to teams across both venues.

This is a complex sort, because the values we are sorting on often have the same values, for example, points or goal difference. Football (or Soccer) is a low scoring game, requiring that many criteria must interact to determine the outcome.

Important: This example has been simplified for our purposes and does not exactly reflect the sorting required to reproduce the exact results table from the English Premier League; for example, we do not incorporate "Head-to-Head Record" or "Fair Play" as criteria.

Step 1: Add Sorting to your Grid

With your discovery open in Discover, do one of the following:

  • From the Home ribbon, click Sort & Filter > Sort.
  • From the Query ribbon, click Sort > Sort.

The Advanced Sort panel opens at the bottom of the page:

Note: The preceding image shows the default options in this panel, given the selected chips in the discovery.

Step 2: Select Targets

Because we will want to sort the overall table by team, and only then sort the venues (home and away) within the team, we need to do the following:

  1. Use the checkboxes (orange highlight below) to select both Chips.

    2. This indicates that we want to sort by both teams and venues.

  2. Use the Increase Indent option (yellow arrow) to indent the Venue Chip under Team.

    3. This shows that we don't want to sort by "team-venues," but first by teams and then, within the teams, by venues.

Important: Note that the background of the Team chip indicates that only it is selected. Previously, when we did not have Venue indented under Team, the black background indicated that both team and venue were selected. The selection of Team means that my choices in the Sort Settings panel will apply only to it.

Step 3: Setting up Sort Settings for Team

The purpose of this step is to demonstrate setting up Sort by and Then by sort settings for the Teams hierarchy. The objective is to sort the teams first by points then, where there are teams with the same number of points, sort by goal difference then, when there are teams with the same goal difference, sort by "goals for" and then, lastly, where there are teams with the same goals for, sort by "goals against."

All sorts should be against the measure and should, in all but the last case, sort the values in Descending order. This is because we want to see the most successful team with the highest points, goals, and so on, but conceding the fewest goals, at the top.

Sort By

Make sure Team is selected in the Target panel and then:

  1. Check that Values and Descending are selected in the Sort By panel (green highlight below).
  2. In Select Hierarchy, choose Measures (blue arrow).
  3. In Select Elements, choose Points (purple arrow).

This selection indicates that this sort results in Teams being put into descending order based on the value of the Points measure.

Then By

To set up the first Then By:

  1. Click Add (yellow arrow below) at the top-right of the Sort Settings panel to add a Then By panel (green highlight).
  2. In the Then By panel, select Values and Descending.
  3. In the Select Hierarchy and Select Elements sections, select Measures and then Goal Diff.

Important: If you were to save these sort details, Teams would be sorted first by Points in descending order and then, where the teams share the same Points value, by goal difference in descending order.

Tip: You can close the panels using the drop-down arrow at the top-left (orange arrow above). You can also change the sequence of the options by dragging any panel up or down relative to the other panels using its grab handle.

Then By +2

Repeat this process two more times for the following measures:

  1. Select Values, Descending, Measures, and Goals For.
  2. Select Values, Ascending, Measures, and Goals Against. (Note that this value is sorted by Ascending order, since it is better to concede fewer goals.)

Step 4: Setting up Sort Settings for Venue

Now that the sort is defined for Team, we need to create Sort Settings for Venue. Remember that, because Venue is indented under Team, it is not part of the main sort. Every team has two Venues (home and away), and they will be ordered by Points and Goal Difference. This means that every Team in the Grid will have a Home and Away venue directly beneath it, but it will depend on points and goal difference whether home or away is shown first.

You need to select Venue in the Target panel and then set up the following Sort Settings:

  • In the Sort By panel, select Values, Descending, Measures, and Points.
  • In a Then By panel, select Values, Descending, Measures, and Goal Diff.

Step 5: Apply your Sort

Once you've configured all your sorting options, click Apply.

Your Grid is sorted. Note that the Sort Info caption that has been added to the canvas (purple arrow below) describes the Sort that you just created using natural language: